import javax.swing.*;
import java.sql.*;

/**
 * 数据库的相关操作
 */


public class dbFunction {

    String url="jdbc:mysql://localhost:3306/st?user=root&password=123456";
    //String driver = "com.mysql.jdbc.Driver";
    private  Connection conn=null;
    public boolean Login(String user, String password) {
        String url = "jdbc:mysql:///st";
        try {
//            Class.forName(driver);

            conn = DriverManager.getConnection(url, user, password);

//            System.out.println(url+","+user+","+password);
            conn.close();
            return true;

        } catch (SQLException e){
            //e.printStackTrace();
            return false;
        }
    }


    public boolean zhw(String datas,int section){
        try {
            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();
            String sql="";//初始化sql

            //执行添加或者修改操作

            if((section>=1&&section<=3)||(section>=7&&section<=9)){
                String[] zhw =datas.split("\\s+"); //用正则表达式对字符串切分
                if(section == 1) {       //添加
                    sql = "insert into student values(";
                    for(int i=0;i<zhw.length;i++){
                        if(i==1||i==2||i==4){
                            sql+="'"+zhw[i]+"'";

                        }else{
                            sql+=zhw[i];

                        }
                        if(i<zhw.length-1)
                            sql+=",";
                        else
                            sql+=" ";
                    }
                }else if(section == 2){  //添加
                    sql = "insert into course values(";
                    for(int i=0;i<zhw.length;i++){
                        if(i==0||i==1||i==3){
                            sql+="'"+zhw[i]+"'";

                        }else{
                            sql+=zhw[i];

                        }
                        if(i<zhw.length-1)
                            sql+=",";
                        else
                            sql+=" ";
                    }
                }else if(section == 3){  //添加
                    sql = "insert into sc values(";
                    for(int i=0;i<zhw.length;i++){
                        sql+=zhw[i];
                        if(i<zhw.length-1)
                            sql+=",";
                        else
                            sql+=" ";
                    }
                }else if(section == 7){  //修改
                    sql = "update student set ";
                    sql+=datas;
                }else if(section == 8){  //修改
                    sql = "update course set ";
                    sql+=datas;
                }else{                   //修改
                    sql = "update sc set ";
                    sql+=datas;
                }

                if(section>=1&&section<=3)
                    sql+=");";
            }else if(section>=4&&section<=6){
                //删除操作
                if(section == 4) {
                    sql = "delete from student where Sno=" + datas;
                    String sql2="select * from Sc where Sno="+datas;
                    ResultSet rest=stmt.executeQuery(sql2);
                    int count=1;
                    //判断Sc表中是否有该Sno
                    if(rest.next()){
                        //是,确定返回0，否返回1
                        count=JOptionPane.showConfirmDialog(new mySystemJFrame(),"Sc中存在该Sno,是否继续？？");
                    }

                    if(count==0){
                        String sql3="delete from sc where Sno="+datas;
                        stmt.executeUpdate(sql3);
                    }

                }else if(section == 5){
                    sql = "delete from course where Cno=" + datas;
                    String sql2="select * from Sc where Cno="+datas;
                    ResultSet rest=stmt.executeQuery(sql2);
                    int count=1;
                    //判断Sc表中是否有该Cno
                    if(rest.next()){
                        //是,确定返回0，否返回1
                        count=JOptionPane.showConfirmDialog(new mySystemJFrame(),"Sc中存在该Cno,是否继续？？");
                    }

                    if(count==0){
                        String sql3="delete from sc where Cno="+datas;
                        stmt.executeUpdate(sql3);
                    }


                }else{
                    sql = "delete from sc where id=" + datas;
                }
            }
            System.out.println(sql);
            if(section<=9)
                stmt.executeUpdate(sql); //非查询操作直接执行

            stmt.close();
            conn.close();
            return true;
        } catch (SQLException e) {
            System.out.println("操作失败！！");
//            e.printStackTrace();
            return false;
        }
    }

    public JTable query(int section,String datas) throws SQLException {
        String sql = "";
        if(section == 10){
            sql = "select Sno as 学号 ,Sname as 姓名,Ssex as 性别 ,Sage as 年龄,Sdept as 所在系 from student";
        }else if (section == 11){
            sql = "select Cno as 课程号 ,Cname as 课程名 ,Cpno as 先行课 ,Ccredit as 学分 from course";
        }else if(section == 12){
            sql = "select Sno as 学号 ,Cno as 课程号 ,Grade as 成绩 from sc";
        }else if(section == 13){
            sql = "select distinct * from student inner join sc on sc.Sno=student.Sno where student.Sno="+datas;
        }else{
            sql = "SELECT Course.Cno as 课程号 ,Course.Cname as 课程名 ,Course.Cpno as 先行课 ,\n" +
                    "       Course.Ccredit as 学分 ,Sno as 学号 ,max(sc.Grade) as 最高分,\n" +
                    "       min(sc.Grade) as 最低分,avg(sc.Grade) as 平均分,\n" +
                    "       concat(count(sc.Grade>=60)/count(sc.Grade)*100,'%') as 及格率\n" +
                    "        FROM course inner join sc on course.Cno=sc.Cno where course.Cno="+datas+" group by sc" +
                ".Sno";
        }
        conn = DriverManager.getConnection(url);
        Statement stmt=conn.createStatement();
        ResultSet rset = stmt.executeQuery(sql);//返回结果集ResultSet
        ResultSetMetaData rsmd = rset.getMetaData();//创建结果集元数据对象
        int columns = rsmd.getColumnCount();          //获得列数
        String columntitle[] = new String[columns];   //创建列名数组
        for(int j=0;j<columns;j++){
            columntitle[j]=rsmd.getColumnName(j+1);   //获得每列的名字
        }
        int rowCount=0;

//        while(temprset.next())                            //迭代遍历结果集，获得结果集总行数
//            rowCount++;

        //rset.first();
        //创建二维数组保存数据结果集
        String results[][] = new String[10000][columns];

        int i=0;//行数
//        rset.next();
        for(;rset.next();i++){    //刚才结果集已经到最后了，反着来
            for(int j=0;j<columns;j++){
                results[i][j] = rset.getString(j+1);//获得当前行指定列的值
//                System.out.println(results[i][j]);
            }

//            System.out.println(results[i]);
        }

       String arr[][]=new String[i+1][columns];
        for(;i>=0;i--){
            for(int j=0;j<columns;j++){
                arr[i][j]=results[i][j];
            }
        }

        rset.close();
        stmt.close();
        return new JTable(arr,columntitle);
    }
}
